package com.apestech.api.tables;

import com.apestech.api.tables.modul.SeqField;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.StringUtil;

import java.util.ArrayList;

/**
 * @author hup
 * @create 2019-02-15-14:55
 */
public class SequenceManage extends StringUtil{

    public class SeqStatu {
        private SeqField seqField;
        private boolean init;

        public SeqStatu(SeqField seqField, boolean init){
            this.seqField=seqField;
            this.init=init;
        }

        public SeqField getSeqField() {
            return seqField;
        }

        public boolean isInit() {
            return init;
        }
    }

    private SqlSession session;
    private String seqName="E_SEQ";
    private JSONObject tables=new JSONObject();

    public SequenceManage(SqlSession session){
        this.session=session;
    }

    public String createSeq(SeqField seqField, boolean bexec) throws Exception {
        if(!isSeqExists(seqName)){
            createSequence(seqName, 10, bexec);
        }
        SeqStatu statu=needInit(seqField);
        if(statu.isInit()){
            //long num=getNextSeqNum(seqName);
            long num=0;
            String nKey=getSeqName(num, seqField.getTableId());
            if(seqField.getType()==0){
                if(!isSeqExists(nKey)){
                    createSequence(nKey, 10, bexec);
                    seqField.setId(num);
                    addSeq(seqField);
                }
            } else {
                seqField.setId(num);
                addSeq(seqField);
            }
            return nKey;
        }
        if(statu.getSeqField().getId()==0) return null;
        String nKey=getSeqName(0, statu.getSeqField().getTableId());
        return nKey;
    }

    public SeqStatu needInit(SeqField seqField) throws Exception {
        if(!isSeqExists(seqName)) return new SeqStatu(seqField, true);
        SeqField f=getFieldFromDB(seqField.getTableId());
        if(f==null) return new SeqStatu(seqField, true);
        return new SeqStatu(f, false);
    }

    public String getNextNumByStr(String tabName) throws Exception {
        SeqField f=getNextField(tabName);
        String snum=castToString(f.getNum());
        if(f.getPadding()>0) return f.getPrefix()+prefixStr0(snum, f.getPadding())+f.getSuffix();
        return f.getPrefix()+snum+f.getSuffix();
    }

    public long getNumByFieldValue(SeqField seqField, Object id) throws Exception {
        if(id==null) return -1;
        if(id instanceof Long) return (long) id;
        String s= (String) id;
        int len=s.length();
        int plen=0;
        if(seqField.getPrefix()!=null){
            plen=seqField.getPrefix().length();
        }
        int slen=0;
        if(seqField.getSuffix()!=null){
            slen=seqField.getSuffix().length();
        }
        if(plen>0){
            len=len-plen;
            s=rightStr(s,len);
        }
        if(slen>0){
            len=len-slen;
            s=leftStr(s,len);
        }
        return castToLong(s);
    }

    public long getNextNum(String tabName) throws Exception {
        return getNextField(tabName).getNum();
    }

    private SeqField getNextField(String tabName) throws Exception {
        SeqField f=getField(tabName);
        if(f==null) throw new Exception("请输入正确的表名信息！");
        if(f.getType()==0){
            String k=getSeqName(f.getId(), f.getTableId());
            f.setNum(getNextSeqNum(k));
        } else {
            f=getFieldFromDB(tabName);
            JSONObject m=new JSONObject();
            m.put("TABLE_ID", tabName);
            m.put("LAST_UPDATED_STAMP_NEW", "SYSDATE");
            m.put("LAST_UPDATED_STAMP", f.getTimestamp());
            String sql="UPDATE EXE_ID SET NUM=NUM+1,LAST_UPDATED_STAMP=#{LAST_UPDATED_STAMP_NEW} WHERE TABLE_ID=#{TABLE_ID} AND LAST_UPDATED_STAMP=#{LAST_UPDATED_STAMP} ";
            if(session.getForUpdate().update(sql, m)==0) throw new Exception("当前数据已改变，请检查！");
            f.setNum(f.getNum()+1);
        }
        return f;
    }

    public void resetMain() throws Exception {
        resetSeq(seqName);
        JSONArray qlist=session.getForJSON().query("SELECT ID,TYPE,TABLE_ID FROM EXE_ID");
        int len=qlist.size();
        for(int i=0; i<len; i++){
            long id=qlist.get(i).getLongValue("ID");
            int type=qlist.get(i).getIntValue("TYPE");
            if(type==0){
                String k=getSeqName(id, qlist.get(i).getString("TABLE_ID"));
                dropSequence(k, true);
            }
        }
        session.getForUpdate().update("DELETE FROM EXE_ID_RANGE ");
        session.getForUpdate().update("DELETE FROM EXE_ID ");
    }

    public void setMainSum(long num) throws Exception {
        setSeqNum(seqName, num);
    }

    public void reset(String tabName) throws Exception {
        SeqField f=getField(tabName);
        if(f==null) throw new Exception("请输入正确的表名信息！");
        if(f.getType()==0){
            String k=getSeqName(f.getId(), f.getTableId());
            resetSeq(k);
        } else {
            f=getFieldFromDB(tabName);
            JSONObject m=new JSONObject();
            m.put("TABLE_ID", tabName);
            m.put("LAST_UPDATED_STAMP_NEW", "SYSDATE");
            m.put("LAST_UPDATED_STAMP", f.getTimestamp());
            m.put("NUM",0);
            String sql="UPDATE EXE_ID SET NUM=#{NUM},LAST_UPDATED_STAMP=#{LAST_UPDATED_STAMP_NEW} WHERE TABLE_ID=#{TABLE_ID} AND LAST_UPDATED_STAMP=#{LAST_UPDATED_STAMP} ";
            if(session.getForUpdate().update(sql, m)==0) throw new Exception("当前数据已改变，请检查！");
        }
    }

    public void setNum(String tabName, long num) throws Exception {
        SeqField f=getField(tabName);
        if(f==null) throw new Exception("请输入正确的表名信息！");
        if(f.getType()==0){
            String k=getSeqName(f.getId(), f.getTableId());
            setSeqNum(k, num);
        } else {
            f=getFieldFromDB(tabName);
            JSONObject m=new JSONObject();
            m.put("TABLE_ID", tabName);
            m.put("LAST_UPDATED_STAMP_NEW", "SYSDATE");
            m.put("LAST_UPDATED_STAMP", f.getTimestamp());
            m.put("NUM",num);
            String sql="UPDATE EXE_ID SET NUM=#{NUM},LAST_UPDATED_STAMP=#{LAST_UPDATED_STAMP_NEW} WHERE TABLE_ID=#{TABLE_ID} AND LAST_UPDATED_STAMP=#{LAST_UPDATED_STAMP} ";
            if(session.getForUpdate().update(sql, m)==0) throw new Exception("当前数据已改变，请检查！");
        }
    }

    public String getSeqName(long id, String tablename){
        if(tablename!=null) {
            return "E_SEQ"+tablename;
        }
        return "E_SEQ"+prefixStr0(castToString(id),4);
    }

    private SeqField getField(String tabName) throws Exception {
        if(tables.get(tabName)==null){
            tables.put(tabName, getFieldFromDB(tabName));
        }
        return (SeqField) tables.get(tabName);
    }

    private SeqField getFieldFromDB(String tabName) throws Exception {
        JSONObject m=new JSONObject();
        m.put("TABLE_ID", tabName);
        JSONArray qlist=session.getForJSON().table("EXE_ID", m);
        if(qlist.size()==0) return null;
        SeqField f=new SeqField();
        f.setField(qlist.get(0));
        return f;
    }

    private void addSeq(SeqField seqField) throws Exception {
        session.getForUpdate().insertTable("EXE_ID", seqField.toJSON());
    }

    private long getNextSeqNum(String name) throws Exception {
        String sql="select "+name+".nextval as NUM from dual ";
        JSONArray list=session.getForJSON().query(sql);
        long num= list.get(0).getLongValue("NUM");
        if(num==1){
            sql="alter sequence "+name+" increment BY 1";
            session.getForDDL().executeDDL(sql);
        }
        return num;
    }

    private boolean isSeqExists(String name) throws Exception {
        String sql="SELECT 1 FROM USER_SEQUENCES WHERE SEQUENCE_NAME=#{SEQUENCE_NAME} ";
        JSONObject m=new JSONObject();
        m.put("SEQUENCE_NAME", name);
        return !session.getForResultSet().queryIsEmpty(sql, m);
    }

    private void resetSeq(String name) throws Exception {
        String sql=null;
        if(session.sequenceExists(name)){
            sql="drop sequence "+name;
            session.getForDDL().executeDDL(sql);
        }
        sql="create sequence "+name+"\n" +
                "minvalue 0\n" +
                "maxvalue 9999999999999999999999999999\n" +
                "start with 1\n" +
                "increment by 1\n" +
                "cache 10";
        session.getForDDL().executeDDL(sql);
    }

    private void setSeqNum(String name, long num) throws Exception {
        resetSeq(name);
        if(num==2) getNextSeqNum(name);
        if(num>2){
            num=num-1;
            String sql="alter sequence "+name+" increment BY "+num;
            session.getForDDL().executeDDL(sql);
            getNextSeqNum(name);
            sql="alter sequence "+name+" increment BY 1";
            session.getForDDL().executeDDL(sql);
        }
    }

    private void createSequence(String name, int cache, boolean bexec) throws Exception {
        String sql= "create sequence "+name+"\n" +
                "minvalue 1\n" +
                "maxvalue 9999999999999999999999999999\n" +
                "start with 1\n" +
                "increment by 1\n" +
                "cache "+cache;
        if(bexec){
            session.getForDDL().executeDDL(sql);
        } else {
            System.out.println(sql+";");
        }
    }

    public void dropSequence(String name) throws Exception {
        dropSequence(name, true);
    }

    private void dropSequence(String name, boolean bexec) throws Exception {
        if(isSeqExists(name)){
            String sql="drop sequence "+name;
            if(bexec){
                if(session.sequenceExists(name)){
                    session.getForDDL().executeDDL(sql);
                }
            } else {
                System.out.println(sql+";");
            }
        }
    }

}
